{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Python数据分析之Pandas-2 "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**首先：读入我们上节课保存的数据文件movie_data.xlsx** "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "ename": "XLRDError",
     "evalue": "Excel xlsx file; not supported",
     "output_type": "error",
     "traceback": [
      "\u001B[0;31m---------------------------------------------------------------------------\u001B[0m",
      "\u001B[0;31mXLRDError\u001B[0m                                 Traceback (most recent call last)",
      "\u001B[0;32m/var/folders/fj/074djdr13178c4hpdlwt37r00000gp/T/ipykernel_10233/222539252.py\u001B[0m in \u001B[0;36m<module>\u001B[0;34m\u001B[0m\n\u001B[0;32m----> 1\u001B[0;31m \u001B[0mdf\u001B[0m \u001B[0;34m=\u001B[0m \u001B[0mpd\u001B[0m\u001B[0;34m.\u001B[0m\u001B[0mread_excel\u001B[0m\u001B[0;34m(\u001B[0m\u001B[0;34mr\"/Users/feiyi-lgh/Documents/projectSrc/pythonSrc/个人学习/python-tutorial/python_常用工具/python数据分析/4.Python数据分析之Pandas-2/movie_data2.xlsx\"\u001B[0m\u001B[0;34m,\u001B[0m\u001B[0mindex_col\u001B[0m \u001B[0;34m=\u001B[0m \u001B[0;36m0\u001B[0m\u001B[0;34m)\u001B[0m\u001B[0;34m\u001B[0m\u001B[0;34m\u001B[0m\u001B[0m\n\u001B[0m",
      "\u001B[0;32m~/opt/anaconda3/envs/py37-aiops/lib/python3.7/site-packages/pandas/io/excel/_base.py\u001B[0m in \u001B[0;36mread_excel\u001B[0;34m(io, sheet_name, header, names, index_col, usecols, squeeze, dtype, engine, converters, true_values, false_values, skiprows, nrows, na_values, keep_default_na, verbose, parse_dates, date_parser, thousands, comment, skipfooter, convert_float, mangle_dupe_cols, **kwds)\u001B[0m\n\u001B[1;32m    302\u001B[0m \u001B[0;34m\u001B[0m\u001B[0m\n\u001B[1;32m    303\u001B[0m     \u001B[0;32mif\u001B[0m \u001B[0;32mnot\u001B[0m \u001B[0misinstance\u001B[0m\u001B[0;34m(\u001B[0m\u001B[0mio\u001B[0m\u001B[0;34m,\u001B[0m \u001B[0mExcelFile\u001B[0m\u001B[0;34m)\u001B[0m\u001B[0;34m:\u001B[0m\u001B[0;34m\u001B[0m\u001B[0;34m\u001B[0m\u001B[0m\n\u001B[0;32m--> 304\u001B[0;31m         \u001B[0mio\u001B[0m \u001B[0;34m=\u001B[0m \u001B[0mExcelFile\u001B[0m\u001B[0;34m(\u001B[0m\u001B[0mio\u001B[0m\u001B[0;34m,\u001B[0m \u001B[0mengine\u001B[0m\u001B[0;34m=\u001B[0m\u001B[0mengine\u001B[0m\u001B[0;34m)\u001B[0m\u001B[0;34m\u001B[0m\u001B[0;34m\u001B[0m\u001B[0m\n\u001B[0m\u001B[1;32m    305\u001B[0m     \u001B[0;32melif\u001B[0m \u001B[0mengine\u001B[0m \u001B[0;32mand\u001B[0m \u001B[0mengine\u001B[0m \u001B[0;34m!=\u001B[0m \u001B[0mio\u001B[0m\u001B[0;34m.\u001B[0m\u001B[0mengine\u001B[0m\u001B[0;34m:\u001B[0m\u001B[0;34m\u001B[0m\u001B[0;34m\u001B[0m\u001B[0m\n\u001B[1;32m    306\u001B[0m         raise ValueError(\n",
      "\u001B[0;32m~/opt/anaconda3/envs/py37-aiops/lib/python3.7/site-packages/pandas/io/excel/_base.py\u001B[0m in \u001B[0;36m__init__\u001B[0;34m(self, io, engine)\u001B[0m\n\u001B[1;32m    822\u001B[0m         \u001B[0mself\u001B[0m\u001B[0;34m.\u001B[0m\u001B[0m_io\u001B[0m \u001B[0;34m=\u001B[0m \u001B[0mstringify_path\u001B[0m\u001B[0;34m(\u001B[0m\u001B[0mio\u001B[0m\u001B[0;34m)\u001B[0m\u001B[0;34m\u001B[0m\u001B[0;34m\u001B[0m\u001B[0m\n\u001B[1;32m    823\u001B[0m \u001B[0;34m\u001B[0m\u001B[0m\n\u001B[0;32m--> 824\u001B[0;31m         \u001B[0mself\u001B[0m\u001B[0;34m.\u001B[0m\u001B[0m_reader\u001B[0m \u001B[0;34m=\u001B[0m \u001B[0mself\u001B[0m\u001B[0;34m.\u001B[0m\u001B[0m_engines\u001B[0m\u001B[0;34m[\u001B[0m\u001B[0mengine\u001B[0m\u001B[0;34m]\u001B[0m\u001B[0;34m(\u001B[0m\u001B[0mself\u001B[0m\u001B[0;34m.\u001B[0m\u001B[0m_io\u001B[0m\u001B[0;34m)\u001B[0m\u001B[0;34m\u001B[0m\u001B[0;34m\u001B[0m\u001B[0m\n\u001B[0m\u001B[1;32m    825\u001B[0m \u001B[0;34m\u001B[0m\u001B[0m\n\u001B[1;32m    826\u001B[0m     \u001B[0;32mdef\u001B[0m \u001B[0m__fspath__\u001B[0m\u001B[0;34m(\u001B[0m\u001B[0mself\u001B[0m\u001B[0;34m)\u001B[0m\u001B[0;34m:\u001B[0m\u001B[0;34m\u001B[0m\u001B[0;34m\u001B[0m\u001B[0m\n",
      "\u001B[0;32m~/opt/anaconda3/envs/py37-aiops/lib/python3.7/site-packages/pandas/io/excel/_xlrd.py\u001B[0m in \u001B[0;36m__init__\u001B[0;34m(self, filepath_or_buffer)\u001B[0m\n\u001B[1;32m     19\u001B[0m         \u001B[0merr_msg\u001B[0m \u001B[0;34m=\u001B[0m \u001B[0;34m\"Install xlrd >= 1.0.0 for Excel support\"\u001B[0m\u001B[0;34m\u001B[0m\u001B[0;34m\u001B[0m\u001B[0m\n\u001B[1;32m     20\u001B[0m         \u001B[0mimport_optional_dependency\u001B[0m\u001B[0;34m(\u001B[0m\u001B[0;34m\"xlrd\"\u001B[0m\u001B[0;34m,\u001B[0m \u001B[0mextra\u001B[0m\u001B[0;34m=\u001B[0m\u001B[0merr_msg\u001B[0m\u001B[0;34m)\u001B[0m\u001B[0;34m\u001B[0m\u001B[0;34m\u001B[0m\u001B[0m\n\u001B[0;32m---> 21\u001B[0;31m         \u001B[0msuper\u001B[0m\u001B[0;34m(\u001B[0m\u001B[0;34m)\u001B[0m\u001B[0;34m.\u001B[0m\u001B[0m__init__\u001B[0m\u001B[0;34m(\u001B[0m\u001B[0mfilepath_or_buffer\u001B[0m\u001B[0;34m)\u001B[0m\u001B[0;34m\u001B[0m\u001B[0;34m\u001B[0m\u001B[0m\n\u001B[0m\u001B[1;32m     22\u001B[0m \u001B[0;34m\u001B[0m\u001B[0m\n\u001B[1;32m     23\u001B[0m     \u001B[0;34m@\u001B[0m\u001B[0mproperty\u001B[0m\u001B[0;34m\u001B[0m\u001B[0;34m\u001B[0m\u001B[0m\n",
      "\u001B[0;32m~/opt/anaconda3/envs/py37-aiops/lib/python3.7/site-packages/pandas/io/excel/_base.py\u001B[0m in \u001B[0;36m__init__\u001B[0;34m(self, filepath_or_buffer)\u001B[0m\n\u001B[1;32m    351\u001B[0m             \u001B[0mself\u001B[0m\u001B[0;34m.\u001B[0m\u001B[0mbook\u001B[0m \u001B[0;34m=\u001B[0m \u001B[0mself\u001B[0m\u001B[0;34m.\u001B[0m\u001B[0mload_workbook\u001B[0m\u001B[0;34m(\u001B[0m\u001B[0mfilepath_or_buffer\u001B[0m\u001B[0;34m)\u001B[0m\u001B[0;34m\u001B[0m\u001B[0;34m\u001B[0m\u001B[0m\n\u001B[1;32m    352\u001B[0m         \u001B[0;32melif\u001B[0m \u001B[0misinstance\u001B[0m\u001B[0;34m(\u001B[0m\u001B[0mfilepath_or_buffer\u001B[0m\u001B[0;34m,\u001B[0m \u001B[0mstr\u001B[0m\u001B[0;34m)\u001B[0m\u001B[0;34m:\u001B[0m\u001B[0;34m\u001B[0m\u001B[0;34m\u001B[0m\u001B[0m\n\u001B[0;32m--> 353\u001B[0;31m             \u001B[0mself\u001B[0m\u001B[0;34m.\u001B[0m\u001B[0mbook\u001B[0m \u001B[0;34m=\u001B[0m \u001B[0mself\u001B[0m\u001B[0;34m.\u001B[0m\u001B[0mload_workbook\u001B[0m\u001B[0;34m(\u001B[0m\u001B[0mfilepath_or_buffer\u001B[0m\u001B[0;34m)\u001B[0m\u001B[0;34m\u001B[0m\u001B[0;34m\u001B[0m\u001B[0m\n\u001B[0m\u001B[1;32m    354\u001B[0m         \u001B[0;32melif\u001B[0m \u001B[0misinstance\u001B[0m\u001B[0;34m(\u001B[0m\u001B[0mfilepath_or_buffer\u001B[0m\u001B[0;34m,\u001B[0m \u001B[0mbytes\u001B[0m\u001B[0;34m)\u001B[0m\u001B[0;34m:\u001B[0m\u001B[0;34m\u001B[0m\u001B[0;34m\u001B[0m\u001B[0m\n\u001B[1;32m    355\u001B[0m             \u001B[0mself\u001B[0m\u001B[0;34m.\u001B[0m\u001B[0mbook\u001B[0m \u001B[0;34m=\u001B[0m \u001B[0mself\u001B[0m\u001B[0;34m.\u001B[0m\u001B[0mload_workbook\u001B[0m\u001B[0;34m(\u001B[0m\u001B[0mBytesIO\u001B[0m\u001B[0;34m(\u001B[0m\u001B[0mfilepath_or_buffer\u001B[0m\u001B[0;34m)\u001B[0m\u001B[0;34m)\u001B[0m\u001B[0;34m\u001B[0m\u001B[0;34m\u001B[0m\u001B[0m\n",
      "\u001B[0;32m~/opt/anaconda3/envs/py37-aiops/lib/python3.7/site-packages/pandas/io/excel/_xlrd.py\u001B[0m in \u001B[0;36mload_workbook\u001B[0;34m(self, filepath_or_buffer)\u001B[0m\n\u001B[1;32m     34\u001B[0m             \u001B[0;32mreturn\u001B[0m \u001B[0mopen_workbook\u001B[0m\u001B[0;34m(\u001B[0m\u001B[0mfile_contents\u001B[0m\u001B[0;34m=\u001B[0m\u001B[0mdata\u001B[0m\u001B[0;34m)\u001B[0m\u001B[0;34m\u001B[0m\u001B[0;34m\u001B[0m\u001B[0m\n\u001B[1;32m     35\u001B[0m         \u001B[0;32melse\u001B[0m\u001B[0;34m:\u001B[0m\u001B[0;34m\u001B[0m\u001B[0;34m\u001B[0m\u001B[0m\n\u001B[0;32m---> 36\u001B[0;31m             \u001B[0;32mreturn\u001B[0m \u001B[0mopen_workbook\u001B[0m\u001B[0;34m(\u001B[0m\u001B[0mfilepath_or_buffer\u001B[0m\u001B[0;34m)\u001B[0m\u001B[0;34m\u001B[0m\u001B[0;34m\u001B[0m\u001B[0m\n\u001B[0m\u001B[1;32m     37\u001B[0m \u001B[0;34m\u001B[0m\u001B[0m\n\u001B[1;32m     38\u001B[0m     \u001B[0;34m@\u001B[0m\u001B[0mproperty\u001B[0m\u001B[0;34m\u001B[0m\u001B[0;34m\u001B[0m\u001B[0m\n",
      "\u001B[0;32m~/opt/anaconda3/envs/py37-aiops/lib/python3.7/site-packages/xlrd/__init__.py\u001B[0m in \u001B[0;36mopen_workbook\u001B[0;34m(filename, logfile, verbosity, use_mmap, file_contents, encoding_override, formatting_info, on_demand, ragged_rows, ignore_workbook_corruption)\u001B[0m\n\u001B[1;32m    168\u001B[0m     \u001B[0;31m# files that xlrd can parse don't start with the expected signature.\u001B[0m\u001B[0;34m\u001B[0m\u001B[0;34m\u001B[0m\u001B[0;34m\u001B[0m\u001B[0m\n\u001B[1;32m    169\u001B[0m     \u001B[0;32mif\u001B[0m \u001B[0mfile_format\u001B[0m \u001B[0;32mand\u001B[0m \u001B[0mfile_format\u001B[0m \u001B[0;34m!=\u001B[0m \u001B[0;34m'xls'\u001B[0m\u001B[0;34m:\u001B[0m\u001B[0;34m\u001B[0m\u001B[0;34m\u001B[0m\u001B[0m\n\u001B[0;32m--> 170\u001B[0;31m         \u001B[0;32mraise\u001B[0m \u001B[0mXLRDError\u001B[0m\u001B[0;34m(\u001B[0m\u001B[0mFILE_FORMAT_DESCRIPTIONS\u001B[0m\u001B[0;34m[\u001B[0m\u001B[0mfile_format\u001B[0m\u001B[0;34m]\u001B[0m\u001B[0;34m+\u001B[0m\u001B[0;34m'; not supported'\u001B[0m\u001B[0;34m)\u001B[0m\u001B[0;34m\u001B[0m\u001B[0;34m\u001B[0m\u001B[0m\n\u001B[0m\u001B[1;32m    171\u001B[0m \u001B[0;34m\u001B[0m\u001B[0m\n\u001B[1;32m    172\u001B[0m     bk = open_workbook_xls(\n",
      "\u001B[0;31mXLRDError\u001B[0m: Excel xlsx file; not supported"
     ]
    }
   ],
   "source": [
    "df = pd.read_excel(r\"/Users/feiyi-lgh/Documents/projectSrc/pythonSrc/个人学习/python-tutorial/python_常用工具/python数据分析/4.Python数据分析之Pandas-2/movie_data2.xlsx\",index_col = 0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df[:5]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2.1 数据格式转换 "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "在做数据分析的时候，原始数据往往会因为各种各样的原因产生各种数据格式的问题。  \n",
    "数据格式是我们非常需要注意的一点，数据格式错误往往会造成很严重的后果。  \n",
    "并且，很多异常值也是我们经过格式转换后才会发现，对我们规整数据，清洗数据有着重要的作用。"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 查看格式 "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df[\"投票人数\"].dtype"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df[\"投票人数\"] = df[\"投票人数\"].astype(\"int\") #转换格式"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df[:5]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df[\"产地\"].dtype"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df[\"产地\"] = df[\"产地\"].astype(\"str\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 将年份转化为整数格式 "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df[\"年代\"] = df[\"年代\"].astype(\"int\") #有异常值会报错"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df[df.年代 == \"2008\\u200e\"] #找到异常数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df[df.年代 == \"2008\\u200e\"][\"年代\"].values #后面是unicode的控制字符，使得其显示靠左，因此需要处理删除"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.loc[[14934,15205],\"年代\"] = 2008 "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.loc[14934]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df[\"年代\"] = df[\"年代\"].astype(\"int\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df[\"年代\"][:5]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 将时长转化为整数格式 "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df[\"时长\"] = df[\"时长\"].astype(\"int\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df[df[\"时长\"] == \"8U\"] #寻找异常值，不知道怎么改的话可以删除"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.drop([31644], inplace = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df[\"时长\"] = df[\"时长\"].astype(\"int\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df[df[\"时长\"] == \"12J\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.drop([32949], inplace = True) #删数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df[\"时长\"] = df[\"时长\"].astype(\"int\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df[:5]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2.2 排序 "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 默认排序 "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df[:10]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 按照投票人数进行排序 "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.sort_values(by = \"投票人数\", ascending = False)[:5] #默认从小到大"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 按照年代进行排序"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.sort_values(by = \"年代\")[:5]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 多个值排序，先按照评分，再按照投票人数 "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.sort_values(by = [\"评分\",\"投票人数\"], ascending = False) #列表中的顺序决定先后顺序"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2.3 基本统计分析 "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "（ 1 ）描述性统计"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "dataframe.describe()：对dataframe中的数值型数据进行描述性统计"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.describe()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 通过描述性统计，可以发现一些异常值，很多异常值往往是需要我们逐步去发现的。 "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df[df[\"年代\"] > 2018] #异常值"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df[df[\"时长\"] > 1000] #异常值"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.drop(df[df[\"年代\"] > 2018].index, inplace = True)\n",
    "df.drop(df[df[\"时长\"] > 1000].index, inplace = True) #删除异常数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.index = range(len(df)) #解决删除后索引不连续的问题"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "（ 2 ）最值"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df[\"投票人数\"].max()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df[\"投票人数\"].min()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df[\"评分\"].max()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df[\"评分\"].min()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df[\"年代\"].min()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "（ 3 ）均值和中值"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df[\"投票人数\"].mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df[\"投票人数\"].median()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df[\"评分\"].mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df[\"评分\"].median()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "（ 4 ）方差和标准差"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df[\"评分\"].var()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df[\"评分\"].std()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "（ 5 ）求和"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df[\"投票人数\"].sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "（ 6 ）相关系数和协方差"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df[[\"投票人数\", \"评分\"]].corr()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df[[\"投票人数\", \"评分\"]].cov()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "（ 7 ）计数"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "len(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df[\"产地\"].unique() #指定唯一值"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "len(df[\"产地\"].unique())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 产地中包含了一些重复的数据，比如美国和USA，德国和西德，俄罗斯和苏联\n",
    "我们可以通过数据替换的方法将这些相同国家的电影数据合并一下。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df[\"产地\"].replace(\"USA\",\"美国\",inplace = True) #第一个参数是要替换的值，第二个参数是替换后的值"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df[\"产地\"].replace([\"西德\",\"苏联\"],[\"德国\",\"俄罗斯\"], inplace = True) #注意一一对应"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "len(df[\"产地\"].unique())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df[\"年代\"].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "len(df[\"年代\"].unique())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "计算每一年电影的数量："
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df[\"年代\"].value_counts(ascending = True)[:10] #默认从大到小"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "电影产出前5的国家或地区："
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df[\"产地\"].value_counts()[:5]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**保存数据**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.to_excel(\"movie_data2.xlsx\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2.4 数据透视 "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Excel中数据透视表的使用非常广泛，其实Pandas也提供了一个类似的功能，名为pivot_table。\n",
    "\n",
    "pivot_table非常有用，我们将重点解释pandas中的函数pivot_table。\n",
    "\n",
    "使用pandas中的pivot_table的一个挑战是，你需要确保你理解你的数据，并清楚地知道你想通过透视表解决什么问题。虽然pivot_table看起来只是一个简单的函数，但是它能够快速地对数据进行强大的分析。"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "1、基础形式"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.set_option(\"max_columns\",100) #设置可展示的行和列，让数据进行完整展示\n",
    "pd.set_option(\"max_rows\",500)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.pivot_table(df, index = [\"年代\"]) #统计各个年代中所有数值型数据的均值（默认）"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "2、也可以有多个索引。实际上，大多数的pivot_table参数可以通过列表获取多个值。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.pivot_table(df, index = [\"年代\", \"产地\"]) #双索引"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "3、也可以指定需要统计汇总的数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.pivot_table(df, index = [\"年代\", \"产地\"], values = [\"评分\"])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "4、还可以指定函数，来统计不同的统计值"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.pivot_table(df, index = [\"年代\", \"产地\"], values = [\"投票人数\"], aggfunc = np.sum)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "通过将“投票人数”列和“评分”列进行对应分组，对“产地”实现数据聚合和总结。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.pivot_table(df, index = [\"产地\"], values = [\"投票人数\", \"评分\"], aggfunc = [np.sum, np.mean])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "5、非数值（NaN）难以处理。如果想移除它们，可以使用“fill_value”将其设置为0。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.pivot_table(df, index = [\"产地\"], aggfunc = [np.sum, np.mean], fill_value = 0)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "6、加入margins = True，可以在下方显示一些总和数据。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.pivot_table(df, index = [\"产地\"], aggfunc = [np.sum, np.mean], fill_value = 0, margins = True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "7、对不同值执行不同的函数：可以向aggfunc传递一个字典。不过，这样做有一个副作用，那就是必须将标签做的更加整洁才行。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.pivot_table(df, index = [\"产地\"], values = [\"投票人数\", \"评分\"], aggfunc = {\"投票人数\":np.sum, \"评分\":np.mean}, fill_value = 0)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "对各个地区的投票人数求和，对评分求均值"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "对各个年份的投票人数求和，对评分求均值"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.pivot_table(df, index = [\"年代\"], values = [\"投票人数\", \"评分\"], aggfunc = {\"投票人数\":np.sum, \"评分\":np.mean}, fill_value = 0, margins = True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "###  透视表过滤 "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "table = pd.pivot_table(df, index = [\"年代\"], values = [\"投票人数\", \"评分\"], aggfunc = {\"投票人数\":np.sum, \"评分\":np.mean}, fill_value = 0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "type(table)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "table[:5]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**1994年被誉为电影史上最伟大的一年，但是通过数据我们可以发现，1994年的平均得分其实并不是很高。1924年的电影均分最高。**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "table[table.index == 1994]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "table.sort_values(\"评分\", ascending = False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**同样的，我们也可以按照多个索引来进行汇总。**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.pivot_table(df, index = [\"产地\", \"年代\"], values = [\"投票人数\", \"评分\"], aggfunc = {\"投票人数\":np.sum, \"评分\":np.mean}, fill_value = 0)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.11"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}